﻿using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Cfg;
using System.Data;
using BlackMofo.DAL.NHUtils;
using OnTheWay.Interfaces;
using OnTheWay.Models;

namespace BlackMofo.DAL
{
    /// <summary>
    /// NHibernate操作辅助类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DALUtil<T> : IObjectBasicBehaviors<T, Guid> where T : OnTheWay.Models.Model
    {
        #region 成员变量
        /// <summary>
        /// 表前缀
        /// </summary>
        private const string m_table_prefix = "t";

        private string m_table = "";

        /// <summary>
        /// NHibernate助手 
        /// </summary>
        private NHHelper m_NHHelper = null;

        /// <summary>
        /// 当前数据库类型
        /// </summary>
        private NHDBType m_CurrentDBType;

        #endregion

        #region 成员属性
        /// <summary>
        /// 当前会话
        /// </summary>
        public ISession CurrentSession
        {
            get
            {
                return m_NHHelper.GetSession();
            }
        }

        /// <summary>
        /// 当前配置
        /// </summary>
        public Configuration Configuration
        {
            get
            {
                return m_NHHelper.Configuration;
            }
        }

        /// <summary>
        /// 当前数据库类型
        /// </summary>
        public NHDBType CurrentDBType
        {
            get
            {
                return m_CurrentDBType;
            }
        }
        #endregion

        #region 构造方法
        public DALUtil()
        {
            //初始化表名
            this.m_table = m_table_prefix + typeof(T).Name;

            NHHelper helper = new NHHelper();
            m_NHHelper = helper;

            Initialize();
        }

        /// <summary>
        /// 初始化操作
        /// </summary>
        public void Initialize()
        {
            GuessDBType();
        }
        #endregion

        #region 事务包装
        public void Transact(Action action)
        {
            Transact<bool>(
              () =>
              {
                  action.Invoke();
                  return false;
              }
              );
        }

        /// <summary>
        /// 传递匿名方法给事务进行操作
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        public TResult Transact<TResult>(Func<TResult> func)
        {
            try
            {
                if (!m_NHHelper.GetSession().Transaction.IsActive)
                {
                    TResult result;
                    using (var tran = m_NHHelper.CurrentSession.BeginTransaction())
                    {
                        try
                        {
                            result = func.Invoke();
                        }
                        catch (Exception ex)
                        {
                            //失败回滚
                            tran.Rollback();
                            throw ex;
                        }
                    }
                    return result;
                }
                return func.Invoke();
            }
            catch
            {
                return func.Invoke();
            }
        }

        #endregion

        #region 传入SQL操作
        public void ExecuteSQL(string sql)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                s.CreateSQLQuery(sql).ExecuteUpdate();
            });
        }

        /// <summary>
        /// 根据sql语句查询总条目
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public long QueryResult(string sql)
        {
            using (var session = CurrentSession)
            {
                IQuery query = session.CreateSQLQuery(sql);
                return query.UniqueResult<int>();
                //IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(long));
                //return query.UniqueResult<long>();
            }
        }
        #endregion

        #region 增删改

        /// <summary>
        /// 保存或更新
        /// </summary>
        /// <param name="model"></param>
        public void UpdateOld(T model)
        {
            Transact(() =>
            {
                using (ISession s = m_NHHelper.CurrentSession)
                using (ITransaction tx = s.BeginTransaction())
                {
                    try
                    {
                        s.Update(model);
                        s.Flush();
                        tx.Commit();
                    }
                    catch (Exception ex)
                    {
                        tx.Rollback();
                        throw ex;
                    }
                }
            });
        }

        /// <summary>
        /// 批量保存或更新
        /// </summary>
        /// <param name="entityList"></param>
        public void SaveOrUpdateBatch(IList<T> entityList)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                foreach (var model in entityList)
                    s.Merge(model);
                s.Flush();
            });
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="model"></param>
        public void Save(T model)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                s.Save(model);
                s.Flush();
            });
        }

        /// <summary>
        /// 保存并返回ID
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Guid SaveAndReturnID(T model)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                s.Save(model);
                s.Flush();
            });
            return model.ID;
        }

        /// <summary>
        /// 批量保存
        /// </summary>
        /// <param name="model"></param>
        public void SaveBatch(List<T> entityList)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                foreach (var model in entityList)
                    s.Save(model);
                s.Flush();
            });
        }

        //批量保存并返回批量ID
        public List<Guid> SaveBatchAndReturnIDList(List<T> entityList)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                foreach (var model in entityList)
                    s.Save(model);
                s.Flush();
            });
            List<Guid> IDList = entityList.Select(x => x.ID).ToList<Guid>();
            return IDList;
        }


        //删除/////////////////////////////////////////////////////////////////////////////////////
        /// <summary>
        /// 对象删
        /// </summary>
        /// <param name="model"></param>
        public void DeleteOld(T model)
        {
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                s.Delete(s.Merge(model));
                s.Flush();
            });
        }

        /// <summary>
        /// ID删除
        /// </summary>
        /// <param name="id">id</param>
        public void DeleteOld(Guid ID)
        {
            string sql = string.Format("delete from {0} where ID='{1}'", m_table_prefix + typeof(T).Name, ID);
            ExecuteSQL(sql);
        }

        //批量删除///////////////////////////////////////////////////////////////////////////////////////
        /// <summary>
        /// 根据ID批量删除
        /// </summary>
        /// <param name="IDList"></param>
        public void DeleteBatch(List<Guid> IDList)
        {
            string sql = string.Format("delete from {0} where ", m_table_prefix + typeof(T).Name);
            string condition = String.Empty;
            foreach (var item in IDList)
                condition += string.Format(" ID='{0}' or", item);
            condition = condition.Substring(0, condition.Length - 2);
            sql = sql + condition;
            ExecuteSQL(sql);
        }

        #endregion

        /// <summary>
        /// 猜测数据库类型
        /// </summary>
        private void GuessDBType()
        {
            string dialect = m_NHHelper.Configuration.Properties["dialect"].ToLower();
            if (dialect.Contains("mssql"))
                m_CurrentDBType = NHDBType.MSSQLServer;
            if (dialect.Contains("oracle"))
                m_CurrentDBType = NHDBType.Oracle;
            if (dialect.Contains("mysql"))
                m_CurrentDBType = NHDBType.MySQL;
            if (dialect.Contains("postgresql"))
                m_CurrentDBType = NHDBType.PostgreSQL;
        }

        #region 查

        /// <summary>
        /// 根据查询是否存在该ID对于的条目
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(Guid id)
        {
            using (var session = CurrentSession)
            {
                return session.Get<T>(id) != null ? true : false;
            }
        }

        /// <summary>
        /// 根据ID获取一个实体对象
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public T GetObject(Guid ID)
        {
            return Transact(() => m_NHHelper.CurrentSession.Get<T>(ID));
        }

        /// <summary>
        /// 根据Name获取一个实体对象
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public T GetObject(string name)
        {
            IList<T> list = new List<T>();
            Transact(() =>
            {
                ISession s = m_NHHelper.CurrentSession;
                list = s.QueryOver<T>().Where(o => o.Name == name).List();
            });
            return (list.Count == 0) ? null : list[0];
        }

        /// <summary>
        /// 获取全部实体对象
        /// </summary>
        /// <returns></returns>
        public IList<T> GetAll()
        {
            return
              m_NHHelper.CurrentSession
              .CreateCriteria<T>()
              .List<T>();
        }

        /// <summary>
        /// 查询表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable SQlQuery(string sql)
        {
            ISession s = null;
            DataSet ds = new DataSet();
            try
            {
                s = CurrentSession;
                IDbCommand command = s.Connection.CreateCommand();
                command.CommandText = sql;
                IDataReader reader = command.ExecuteReader();
                DataTable result = new DataTable();
                DataTable schemaTable = reader.GetSchemaTable();

                //复制列
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                    result.Columns.Add(schemaTable.Rows[i][0].ToString());
                //复制行
                while (reader.Read())
                {
                    int fieldCount = reader.FieldCount;
                    object[] values = new Object[fieldCount];
                    for (int i = 0; i < fieldCount; i++)
                    {
                        values[i] = reader.GetValue(i);
                    }
                    result.Rows.Add(values);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (s != null)
                {
                    s.Close();
                }
            }
        }

        /// <summary>
        /// 物理存在的实体对象数量
        /// </summary>
        /// <returns></returns>
        public int GetCount()
        {
            return Transact(() =>
              m_NHHelper.CurrentSession.QueryOver<T>().RowCount()
            );
        }

        public long GetRecordCount(string where)
        {
            string sql = "select count(*) from " + m_table + " " + where;
            try
            {
                return this.QueryResult(sql);
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public IList<T> GetListByPage(string where, Page page)
        {
            string asc = "asc";
            if (!page.IsASC)
            {
                asc = "desc";
            }

            string order = " RegDate ";

            if (page.Order != "")
            {
                order = page.Order;
            }
            else
            {
                asc = "desc";
            }

            string sql = "select * from " + this.m_table + " " + where + " ORDER BY " + order + " " + asc;
            using (var session = CurrentSession)
            {
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(T));
                query.SetFirstResult(page.FirstResult);
                query.SetMaxResults(page.MaxResult);
                IList<T> rtlist = query.List<T>();
                return rtlist == null ? new List<T>() : rtlist;
            }
        }

        public IList<T> GetListWhere(string where, string order, string asc)
        {
            //IList<Guid> IDList = GetIDListWhere(where, "", "");
            //IList<T> TList = Cache.Data.GetCacheData<T>(IDList);
            //if (null != TList && TList.Count == IDList.Count)
            //{ return TList.OrderBy(x => x.Name).ToList(); }

            if (!string.IsNullOrEmpty(order))
            {
                order = " ORDER BY " + order + " " + asc;
            }

            string sql = "select * from " + this.m_table + " " + where + order;
            using (var session = CurrentSession)
            {
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(T));
                IList<T> rtlist = query.List<T>();
                return rtlist == null ? new List<T>() : rtlist;
            }
        }

        /// <summary>
        /// 根据指定条件获取ID列表
        /// </summary>
        /// <param name="where">筛选条件</param>
        /// <param name="order">排序字段</param>
        /// <param name="asc">升序或降序</param>
        /// <returns>满足指定条件的ID列表</returns>
        public IList<Guid> GetIDListWhere(string where, string order, string asc)
        {
            if (!string.IsNullOrEmpty(order))
            {
                order = " ORDER BY " + order + " " + asc;
            }

            string sql = "select ID from " + this.m_table + " " + where + order;
            using (var session = CurrentSession)
            {
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(Guid));
                IList<Guid> rtlist = query.List<Guid>();
                return rtlist == null ? new List<Guid>() : rtlist;
            }
        }

        #endregion

        public bool Insert(T model)
        {
            this.Save(model);
            return true;
        }

        public bool Update(T model)
        {
            this.UpdateOld(model);
            return true;
        }

        public bool Delete(T model)
        {
            this.DeleteOld(model);
            return true;
        }

        public T GetByID(Guid id)
        {
            return this.GetObject(id);
        }

        public bool DeleteList(IList<T> list)
        {
            List<Guid> ids = new List<Guid>();
            foreach (var id in list)
            {
                ids.Add(id.ID);
            }
            this.DeleteBatch(ids);
            return true;
        }

        public bool DeleteByID(Guid id)
        {
            this.DeleteOld(id);
            return true;
        }

        public IList<T> GetInValidData()
        {
            throw new NotImplementedException();
        }

        public IList<T> GetInValidDataPage(Page page)
        {
            throw new NotImplementedException();
        }

        public bool UpdateList(IList<T> list)
        {
            this.SaveOrUpdateBatch(list);
            return true;
        }
    }
}
